package org.codefinger.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import javax.sql.DataSource;

import org.codefinger.dao.antlr.v4.runtime.ANTLRInputStream;
import org.codefinger.dao.antlr.v4.runtime.CommonTokenStream;
import org.codefinger.dao.impl.TableInfo.TablePojo;
import org.codefinger.dao.impl.TableInfo.ValueGetter;
import org.codefinger.dao.impl.TableInfo.ValueSetter;
import org.codefinger.dao.impl.sql.IValue;
import org.codefinger.dao.impl.sql.UValue;
import org.codefinger.dao.impl.sql.Where;
import org.codefinger.dao.util.AbstractCache.ValueBuilder;
import org.codefinger.dao.util.ArrayIterator;
import org.codefinger.dao.util.EqualsCache;
import org.codefinger.dao.util.jdbc.JDBCUtil;
import org.codefinger.json.util.Lang;

public abstract class DataAccesser {

	protected static final QueryParserErrorListener	ERROR_LISTENER	= new QueryParserErrorListener();

	protected final EqualsCache<String, TableInfo>	tableInfoCache	= new EqualsCache<String, TableInfo>(new TableInfoBuilder(this));

	protected final EqualsCache<ParamWhere, Where>	whereCache		= new EqualsCache<ParamWhere, Where>(new WhereBuilder(this));

	protected final EqualsCache<String, UValue>		uValueCache		= new EqualsCache<String, UValue>(new UValueBuilder(this));

	protected final EqualsCache<InsertInfo, IValue>	iValueCache		= new EqualsCache<InsertInfo, IValue>(new IValueBuilder(this));

	protected DataSource							dataSource;

	public DataAccesser(DataSource dataSource) {
		super();
		this.dataSource = dataSource;
	}

	protected abstract QueryBuilder createQueryBuilder();

	protected abstract QueryBuilder createWhereBuilder();

	protected abstract QueryBuilder createUValueBuilder();

	protected abstract QueryBuilder createIValueBuilder(String table);

	protected abstract TableInfo createTableInfo(String table);

	public TableInfo getTableInfo(String table) {
		return tableInfoCache.get(table);
	}

	public Querier createQuerier(String sql) {
		QueryParser queryParser = new QueryParser(new CommonTokenStream(new QueryLexer(new ANTLRInputStream(sql))), this);

		QueryBuilder builder = createQueryBuilder();

		queryParser.addErrorListener(ERROR_LISTENER);
		queryParser.setBuilder(builder);
		queryParser.query();

		return builder.buildQuerier();
	}

	public Where createWhere(String condition, int paramIndex) {
		return whereCache.get(new ParamWhere(paramIndex, condition));
	}

	public UValue createUValue(String sql) {
		return uValueCache.get(sql);
	}

	public <T> T getByID(String table, Class<T> clas, long id) {
		TableInfo tableInfo = getTableInfo(table);
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(tableInfo.getSelectSQL());
			statement.setLong(1, id);
			resultSet = statement.executeQuery();
			TablePojo<T> tablePojo = tableInfo.getTablePojo(clas);
			Iterator<ValueSetter> iterator = tablePojo.getValueSetters();
			if (resultSet.next()) {
				T target = tablePojo.newInstance();
				while (iterator.hasNext()) {
					iterator.next().setValue(target, resultSet);
				}
				tablePojo.setKeyValue(target, id);
				return target;
			}
			return null;
		} catch (Throwable throwable) {
			throw Lang.wrapThrow(throwable, "Fetch the pojo from the table %s by id %d error.", table, id);
		} finally {
			JDBCUtil.freeResource(connection, statement, resultSet);
		}
	}

	public Map<String, Object> getByID(String table, long id) {
		TableInfo tableInfo = getTableInfo(table);
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(tableInfo.getSelectSQL());
			statement.setLong(1, id);
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				Iterator<String> aliases = tableInfo.getColumnsExcKey();
				int index = 1;
				ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
				while (aliases.hasNext()) {
					map.put(aliases.next(), JDBCUtil.getResult(resultSet, index, resultSetMetaData.getColumnType(index++)));
				}
				map.put(tableInfo.getPrimaryKey(), id);
				return map;
			}
			return null;
		} catch (Throwable throwable) {
			throw Lang.wrapThrow(throwable, "Fetch the pojo from the table %s by id %d error.", table, id);
		} finally {
			JDBCUtil.freeResource(connection, statement, resultSet);
		}
	}

	public boolean deleteByID(String table, long id) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(getTableInfo(table).getDeleteIdSQL());
			statement.setLong(1, id);
			return statement.executeUpdate() == 1;
		} catch (Throwable throwable) {
			throw Lang.wrapThrow(throwable, "Delete from the table '%s' by ID '%d' error.", table, id);
		} finally {
			JDBCUtil.freeResource(connection, statement, null);
		}
	}

	public void deleteByID(String table, long[] idArray) {
		int length = idArray.length;
		if (length > 0) {
			Connection connection = null;
			PreparedStatement statement = null;
			try {
				connection = dataSource.getConnection();
				connection.setAutoCommit(false);
				statement = connection.prepareStatement(getTableInfo(table).getDeleteIdSQL());
				statement.setLong(1, idArray[0]);
				statement.addBatch();
				for (int i = 1; i < length; i++) {
					statement.setLong(1, idArray[i]);
					statement.addBatch();
				}
				statement.executeBatch();
				connection.commit();
			} catch (Throwable throwable) {
				throw Lang.wrapThrow(throwable, "Delete from the table '%s' by ID array error.", table);
			} finally {
				JDBCUtil.freeResource(connection, statement, null);
			}
		}
	}

	public void deleteByID(String table, Iterable<Long> idList) {
		Iterator<Long> iterator = idList.iterator();
		if (iterator.hasNext()) {
			Connection connection = null;
			PreparedStatement statement = null;
			try {
				connection = dataSource.getConnection();
				connection.setAutoCommit(false);
				statement = connection.prepareStatement(getTableInfo(table).getDeleteIdSQL());
				statement.setLong(1, iterator.next());
				statement.addBatch();
				while (iterator.hasNext()) {
					statement.setLong(1, iterator.next());
					statement.addBatch();
				}
				statement.executeBatch();
				connection.commit();
			} catch (Throwable throwable) {
				throw Lang.wrapThrow(throwable, "Delete from the table '%s' by ID list error.", table);
			} finally {
				JDBCUtil.freeResource(connection, statement, null);
			}
		}
	}

	public int deleteAll(TableInfo tableInfo) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(tableInfo.getDeleteAllSQL());
			return statement.executeUpdate();
		} catch (Throwable throwable) {
			throw Lang.wrapThrow(throwable, "Delete all from the table '%s' error.", tableInfo.getTableName());
		} finally {
			JDBCUtil.freeResource(connection, statement, null);
		}
	}

	public boolean update(String table, Object pojo) {
		TableInfo tableInfo = getTableInfo(table);
		TablePojo<?> tablePojo = tableInfo.getTablePojo(pojo.getClass());
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(tableInfo.getUpdateSQL());
			Iterator<ValueGetter> iterator = tablePojo.getValueGetters();
			int index = 1;
			while (iterator.hasNext()) {
				JDBCUtil.setStatementParam(statement, iterator.next().getValue(pojo), index++);
			}
			JDBCUtil.setStatementParam(statement, tablePojo.getKeyValue(pojo), index);
			return statement.executeUpdate() == 1;
		} catch (Throwable throwable) {
			throw Lang.wrapThrow(throwable, "Update from the table '%s' error.", table);
		} finally {
			JDBCUtil.freeResource(connection, statement, null);
		}
	}

	public <T> void update(String table, Iterator<T> pojos) {
		if (pojos.hasNext()) {
			Object pojo = pojos.next();
			TableInfo tableInfo = getTableInfo(table);
			TablePojo<?> tablePojo = tableInfo.getTablePojo(pojo.getClass());
			Connection connection = null;
			PreparedStatement statement = null;
			try {
				connection = dataSource.getConnection();
				connection.setAutoCommit(false);
				statement = connection.prepareStatement(tableInfo.getUpdateSQL());
				ArrayIterator<ValueGetter> iterator = tablePojo.getValueGetters();
				int index = 1;
				while (iterator.hasNext()) {
					JDBCUtil.setStatementParam(statement, iterator.next().getValue(pojo), index++);
				}
				JDBCUtil.setStatementParam(statement, tablePojo.getKeyValue(pojo), index);
				statement.addBatch();
				while (pojos.hasNext()) {
					pojo = pojos.next();
					iterator.reset();
					index = 1;
					while (iterator.hasNext()) {
						JDBCUtil.setStatementParam(statement, iterator.next().getValue(pojo), index++);
					}
					JDBCUtil.setStatementParam(statement, tablePojo.getKeyValue(pojo), index);
					statement.addBatch();
				}
				statement.executeBatch();
				connection.commit();
			} catch (Throwable throwable) {
				throw Lang.wrapThrow(throwable, "Update from the table '%s' error.", table);
			} finally {
				JDBCUtil.freeResource(connection, statement, null);
			}
		}
	}

	public boolean insert(String table, Object pojo) {
		TableInfo tableInfo = getTableInfo(table);
		TablePojo<?> tablePojo = tableInfo.getTablePojo(pojo.getClass());
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(tableInfo.getInsertSQL(), Statement.RETURN_GENERATED_KEYS);
			Iterator<ValueGetter> iterator = tablePojo.getValueGetters();
			int index = 1;
			while (iterator.hasNext()) {
				JDBCUtil.setStatementParam(statement, iterator.next().getValue(pojo), index++);
			}
			if (statement.executeUpdate() == 1) {
				resultSet = statement.getGeneratedKeys();
				resultSet.next();
				tablePojo.setKeyValue(pojo, resultSet);
				return true;
			}
		} catch (Throwable throwable) {
			throw Lang.wrapThrow(throwable, "Insert into the table '%s' error.", table);
		} finally {
			JDBCUtil.freeResource(connection, statement, resultSet);
		}
		return false;
	}

	public <T> void insert(String table, Iterator<T> pojos) {
		if (pojos.hasNext()) {
			T pojo = pojos.next();
			TableInfo tableInfo = getTableInfo(table);
			TablePojo<?> tablePojo = tableInfo.getTablePojo(pojo.getClass());
			Connection connection = null;
			PreparedStatement statement = null;
			try {
				connection = dataSource.getConnection();
				connection.setAutoCommit(false);
				statement = connection.prepareStatement(tableInfo.getInsertSQL());
				ArrayIterator<ValueGetter> iterator = tablePojo.getValueGetters();
				int index = 1;
				while (iterator.hasNext()) {
					JDBCUtil.setStatementParam(statement, iterator.next().getValue(pojo), index++);
				}
				statement.addBatch();
				while (pojos.hasNext()) {
					pojo = pojos.next();
					index = 1;
					iterator.reset();
					while (iterator.hasNext()) {
						JDBCUtil.setStatementParam(statement, iterator.next().getValue(pojo), index++);
					}
					statement.addBatch();
				}
				statement.executeBatch();
				connection.commit();
			} catch (Throwable throwable) {
				throw Lang.wrapThrow(throwable, "Insert into the table '%s' error.", table);
			} finally {
				JDBCUtil.freeResource(connection, statement, null);
			}
		}
	}

	public IValue createIValue(String table, String setValues) {
		return iValueCache.get(new InsertInfo(table, setValues));
	}

	public int executeUpdate(String sql, Object[] params) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(sql);
			JDBCUtil.setStatementParams(statement, params);
			return statement.executeUpdate();
		} catch (Throwable throwable) {
			throw Lang.wrapThrow(throwable, "Execute sql error, '%s'.", sql);
		} finally {
			JDBCUtil.freeResource(connection, statement, null);
		}
	}

	public long executeInsert(String sql, Object[] params) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			JDBCUtil.setStatementParams(statement, params);
			if (statement.executeUpdate() == 1) {
				resultSet = statement.getGeneratedKeys();
				if (resultSet.next()) {
					return resultSet.getLong(1);
				}
			}
			throw Lang.makeThrow("Execute sql error, '%s'.", sql);
		} catch (Throwable throwable) {
			throw Lang.wrapThrow(throwable, "Execute sql error, '%s'.", sql);
		} finally {
			JDBCUtil.freeResource(connection, statement, resultSet);
		}
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public ArrayIterator<String> getColumns(String tableName) {
		return getTableInfo(tableName).getColumns();
	}

	private static class TableInfoBuilder implements ValueBuilder<String, TableInfo> {

		private DataAccesser	dataAccesser;

		public TableInfoBuilder(DataAccesser dataAccesser) {
			super();
			this.dataAccesser = dataAccesser;
		}

		@Override
		public TableInfo build(String table) {
			return dataAccesser.createTableInfo(table);
		}

	}

	private static class ParamWhere {

		private final int		paramIndex;

		private final String	where;

		private final int		hashCode;

		public ParamWhere(int paramIndex, String where) {
			super();
			this.paramIndex = paramIndex;
			this.where = where;
			this.hashCode = 31 * where.hashCode() + paramIndex;
		}

		@Override
		public int hashCode() {
			return hashCode;
		}

		@Override
		public boolean equals(Object obj) {
			if (obj instanceof ParamWhere) {
				ParamWhere paramWhere = (ParamWhere) obj;
				return paramIndex == paramWhere.paramIndex && where.equals(paramWhere.where);
			}
			return false;
		}

	}

	private static class InsertInfo {

		private final String	table;

		private final String	setValues;

		private final int		hashCode;

		public InsertInfo(String table, String setValues) {
			super();
			this.table = table;
			this.setValues = setValues;
			this.hashCode = table.hashCode() * 31 + setValues.hashCode();
		}

		@Override
		public int hashCode() {
			return hashCode;
		}

		@Override
		public boolean equals(Object obj) {
			if (obj instanceof InsertInfo) {
				InsertInfo insertInfo = (InsertInfo) obj;
				return table.equals(insertInfo.table) && setValues.equals(insertInfo.setValues);
			}
			return false;
		}

	}

	private static class IValueBuilder implements ValueBuilder<InsertInfo, IValue> {

		private DataAccesser	dataAccesser;

		public IValueBuilder(DataAccesser dataAccesser) {
			super();
			this.dataAccesser = dataAccesser;
		}

		@Override
		public IValue build(InsertInfo insertInfo) {
			QueryParser queryParser = new QueryParser(new CommonTokenStream(new QueryLexer(new ANTLRInputStream(insertInfo.setValues))), dataAccesser);

			QueryBuilder builder = dataAccesser.createIValueBuilder(insertInfo.table);

			queryParser.addErrorListener(ERROR_LISTENER);
			queryParser.setBuilder(builder);
			queryParser.insert();

			return builder.buildIValue();
		}

	}

	private static class UValueBuilder implements ValueBuilder<String, UValue> {

		private DataAccesser	dataAccesser;

		public UValueBuilder(DataAccesser dataAccesser) {
			super();
			this.dataAccesser = dataAccesser;
		}

		@Override
		public UValue build(String sql) {
			QueryParser queryParser = new QueryParser(new CommonTokenStream(new QueryLexer(new ANTLRInputStream(sql))), dataAccesser);

			QueryBuilder builder = dataAccesser.createUValueBuilder();

			queryParser.addErrorListener(ERROR_LISTENER);
			queryParser.setBuilder(builder);
			queryParser.setValues();

			return builder.buildUValue(queryParser.paramIndex);
		}

	}

	private static class WhereBuilder implements ValueBuilder<ParamWhere, Where> {

		private DataAccesser	dataAccesser;

		public WhereBuilder(DataAccesser dataAccesser) {
			super();
			this.dataAccesser = dataAccesser;
		}

		@Override
		public Where build(ParamWhere paramWhere) {
			QueryParser queryParser = new QueryParser(new CommonTokenStream(new QueryLexer(new ANTLRInputStream(paramWhere.where))), dataAccesser);
			queryParser.setParamIndex(paramWhere.paramIndex);

			QueryBuilder builder = dataAccesser.createWhereBuilder();

			queryParser.addErrorListener(ERROR_LISTENER);
			queryParser.setBuilder(builder);
			queryParser.condition();

			return new Where(builder.popExp());

		}

	}

}
